package seismosurfer.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import seismosurfer.data.DuplicateData;
import seismosurfer.data.MagData;
import seismosurfer.data.QuakeData;
import seismosurfer.data.SourceData;
import seismosurfer.util.SeismoException;
import seismosurfer.util.TimeUtil;

/**
 * A DAO class that encapsulates all access to the
 * QUAKE table.
 *
 */
public class QuakeDAO extends DAOObject {

    protected static final String FIND_GINOA_DUPLICATE = " SELECT q.quakeid, q.catalog_id, q.has_macro, q.has_info "
            + " FROM quake q, catalog c "
            + " WHERE q.catalog_id = c.catid AND c.source = ? "
            + " AND (q.datetime > ? - INTERVAL '5' SECOND ) "
            + " AND (q.datetime < ? + INTERVAL '5' SECOND ) "
            + " AND (q.epicenter.SDO_POINT.X > ? ) AND (q.epicenter.SDO_POINT.X < ? ) "
            + " AND (q.epicenter.SDO_POINT.Y > ? ) AND (q.epicenter.SDO_POINT.Y < ? ) ";

    protected static final String INSERT_QUAKE = " INSERT INTO quake "
            + " VALUES "
            + " (QUAKE_SEQ.NEXTVAL, default, ?, "
            + " MDSYS.SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE( ?, ?, NULL), NULL, NULL), "
            + " ?, ?, default, ?, ?, default, default, default, default)";

    protected static final String INSERT_MAG = " INSERT INTO mag " + " VALUES "
            + " (MAG_SEQ.NEXTVAL, QUAKE_SEQ.CURRVAL, ?, ?, ?, "
            + "  default, ?, default, default)";

    protected static final String FIND_QUAKE =

    " SELECT * " + " FROM quake_mag_catalog q " + " WHERE q.quakeid = ? ";

    protected static final String FIND_SOURCES = " SELECT max (lddate), source "
            + " FROM quake_mag_catalog " + " GROUP BY source ";

    protected static final String DELETE_DUPLICATE = " DELETE FROM quake "
            + " WHERE quakeid = ? ";
    
    /**
     * Default empty constructor.
     *
     */
    public QuakeDAO() {
    }
    
    /**
     * Deletes the quake record with the given id.
     * 
     * @param quakeid the id of the quake to be deleted
     */
    public void deleteQuake(long quakeid) {
        PreparedStatement ps = null;

        try {
            ps = DB.prepare(DELETE_DUPLICATE);
            ps.setLong(1, quakeid);
            ps.executeUpdate();
        } catch (SQLException ex) {
            throw new SeismoException(ex);
        } finally {
            DB.cleanUp(ps);
        }

    }
    
    /**
     * Finds duplicate records in the database given a
     * quake`s data.
     * 
     * @param quake a QuakeData object that holds a quake`s data
     * @return a List with DuplicateData objects
     */
    public List findDuplicates(QuakeData quake) {
        PreparedStatement ps = null;
        ResultSet rs = null;

        List result = new ArrayList();

        try {
            ps = DB.prepare(FIND_GINOA_DUPLICATE);
            ps.setString(1, quake.getSource());
            ps.setTimestamp(2, new Timestamp(quake.getDateTime()));
            ps.setTimestamp(3, new Timestamp(quake.getDateTime()));

            ps.setDouble(4, quake.getLongitude() - DEGREE_SPACE_CRITERION);
            ps.setDouble(5, quake.getLongitude() + DEGREE_SPACE_CRITERION);
            ps.setDouble(6, quake.getLatitude() - DEGREE_SPACE_CRITERION);
            ps.setDouble(7, quake.getLatitude() + DEGREE_SPACE_CRITERION);

            rs = ps.executeQuery();

            while (rs.next()) {
                result.add(loadDuplicates(rs));
            }

            return result;
        } catch (SQLException ex) {
            throw new SeismoException(ex);
        } finally {
            DB.cleanUp(ps, rs);
        }
    }
    
    /**
     * Loads a DuplicateData object with its data 
     * from a ResultSet row. 
     * 
     * @param rs the ResultSet from which the data are loaded
     * @return a DuplicateData object 
     * @throws SQLException
     */
    protected DuplicateData loadDuplicates(ResultSet rs) throws SQLException {
        DuplicateData d = new DuplicateData();
        d.setQuakeID(rs.getLong(1));
        d.setCatalogID(rs.getInt(2));
        d.setMacro(toBoolean(rs.getInt(3)));
        d.setInfo(toBoolean(rs.getInt(4)));

        return d;
    }
    
    /**
     * Inserts a new row in the MAG table.
     * 
     * @param mag the MagData that contains the magnitude data for an earthquake
     */
    public void insertMagnitude(MagData mag) {

        PreparedStatement ps = null;

        try {
            // Insert the new magnitude data into the MAG table
            ps = DB.prepare(INSERT_MAG);
            ps.setDouble(1, mag.getMagnitude());
            ps.setString(2, mag.getMagnitudeType());
            ps.setInt(3, toInt(mag.isCalculated()));
            ps.setString(4, mag.getMagnitudeAgency());

            ps.executeUpdate();
        } catch (SQLException ex) {
            throw new SeismoException(ex);
        } finally {
            DB.cleanUp(ps);
        }

    }
    
    /**
     * Inserts a new row in the QUAKE table.
     * 
     * @param quake the QuakeData that holds an earthquake`s data.
     */
    public void insertQuake(QuakeData quake) {
        PreparedStatement ps = null;

        try {
            ps = DB.prepare(INSERT_QUAKE);

            ps.setTimestamp(1, new Timestamp(quake.getDateTime()));
            ps.setDouble(2, quake.getLongitude());
            ps.setDouble(3, quake.getLatitude());
            ps.setDouble(4, quake.getDepth());
            ps.setInt(5, quake.getCatalogID());

            ps.setString(6, quake.getAgency());
            ps.setInt(7, quake.getFECode());

            ps.executeUpdate();
        } catch (SQLException ex) {
            throw new SeismoException(ex);
        } finally {
            DB.cleanUp(ps);
        }
    }
    
    /**
     * Gets a list with all the data sources that 
     * are used to collect earthquake data.
     * 
     * @return a list of SourceData
     */
    public List getSources() {
        List result = new ArrayList();
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {

            stmt = DB.prepare(FIND_SOURCES);
            rs = stmt.executeQuery();

            while (rs.next()) {
                result.add(loadSource(rs));
            }
            return result;
        } catch (SQLException e) {
            throw new SeismoException(e);
        } finally {
            DB.cleanUp(stmt, rs);
        }
    }
    
    /**
     * Loads a SourceData object with data from a db row.
     * 
     * @param rs the ResultSet from which the data will be loaded
     * @return a SourceData object which contains data relative to a
     *         data source
     * @throws SQLException
     */
    public static SourceData loadSource(ResultSet rs) throws SQLException {
        long update = rs.getTimestamp(1).getTime();
        String source = rs.getString(2);
        SourceData sd = new SourceData(source, update);

        return sd;
    }
    
    /**
     * Loads a row from the database into a QuakeData object.
     * 
     * @param rs the ResultSet from which the row will be loaded.
     * @return A QuakeData object which contains the data of a row.
     * @throws SQLException
     */
    public static QuakeData loadQuake(ResultSet rs) throws SQLException {

        QuakeData quake = new QuakeData();

        quake.setQuakeID(rs.getLong(1));

        quake.setHasMacroseismic(toBoolean(rs.getInt(2)));
        quake.setHasInfo(toBoolean(rs.getInt(3)));

        // Timestamp doesn`t work with Oracle 10g JDBC driver,
        // when more than a few hundred objects are created...
        long datetime = TimeUtil.oracleToJava(rs.getDate(4), rs.getTime(4));
        quake.setDateTime(datetime);

        quake.setLongitude(rs.getDouble(5));
        quake.setLatitude(rs.getDouble(6));

        quake.setDepth(rs.getDouble(7));

        quake.setAgency(rs.getString(8));

        quake.setMagnitude(rs.getDouble(9));

        quake.setCatalogName(rs.getString(10));

        quake.setFECode(rs.getInt(12));

        quake.setSource(rs.getString(13));

        Timestamp lddate = rs.getTimestamp(14);
        quake.setLoadDate(lddate.getTime());

        return quake;
    }
    
    /**
     * Retrives a quake`s data given its id.
     * 
     * @param quakeid the id of the quake
     * @return a QuakeData object with a quake`s data
     */
    public static QuakeData findQuake(long quakeid) {

        PreparedStatement stmt = null;
        ResultSet rs = null;
        QuakeData quake = null;

        try {

            stmt = DB.prepare(FIND_QUAKE);
            stmt.setLong(1, quakeid);

            rs = stmt.executeQuery();

            if (rs.next()) {
                quake = loadQuake(rs);
            }

            return quake;

        } catch (SQLException e) {
            throw new SeismoException(e);
        } finally {
            DB.cleanUp(stmt, rs);
        }

    }

}